package batch.import_user;

import java.sql.*;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.dbutils.*;

public class checkManager
{
    public checkManager(){
        ;
    }

    public String removeCharacters(String strParam){
        strParam = StringUtils.replace(strParam, " " , "");
        strParam = StringUtils.replace(strParam, "-" , "");
        strParam = StringUtils.replace(strParam, "." , "");
        strParam = StringUtils.replace(strParam, "'" , "");
        strParam = StringUtils.replace(strParam, "\"", "");
        strParam = StringUtils.replace(strParam, "/" , "");
        strParam = StringUtils.replace(strParam, "@" , "");
        strParam = StringUtils.replace(strParam, "?" , "");
        strParam = StringUtils.replace(strParam, "`" , "");
        strParam = StringUtils.replace(strParam, ";" , "");
        strParam = StringUtils.replace(strParam, "#" , "");
        strParam = StringUtils.replace(strParam, "(" , "");
        strParam = StringUtils.replace(strParam, ")" , "");
        strParam = StringUtils.replace(strParam, "<" , "");
        strParam = StringUtils.replace(strParam, ">" , "");
        strParam = StringUtils.replace(strParam, "!" , "");
        strParam = StringUtils.replace(strParam, "$" , "");
        strParam = StringUtils.replace(strParam, "%" , "");
        strParam = StringUtils.replace(strParam, "^" , "");
        strParam = StringUtils.replace(strParam, "&" , "");
        strParam = StringUtils.replace(strParam, "*" , "");
        strParam = StringUtils.replace(strParam, "+" , "");
        strParam = StringUtils.replace(strParam, "=" , "");
        strParam = StringUtils.replace(strParam, "}" , "");
        strParam = StringUtils.replace(strParam, "{" , "");
        strParam = StringUtils.replace(strParam, "~" , "");
        strParam = StringUtils.replace(strParam, "|" , "");
        strParam = StringUtils.replace(strParam, "[" , "");
        strParam = StringUtils.replace(strParam, "]" , "");
        strParam = StringUtils.replace(strParam, ":" , "");
        strParam = StringUtils.replace(strParam, "," , "");

        return strParam;
    }

    public boolean chkMemberId(Connection conn, String strUserId) throws Exception{
        boolean blRe = false;

        String strQuery = "SELECT COUNT(ID) FROM G_MB_MEMBER WHERE ID = '" + StringUtils.replace(strUserId, "'", "''") + "'";

        try{
            QueryRunner qRunner = new QueryRunner();

            ResultSetHandler rsh = new ScalarHandler(1);

            int cnt = ((Number)qRunner.query(conn, strQuery, rsh)).intValue();

            if(cnt > 0){
                blRe = true;
            }
        }
        finally{
            ;
        }

        return blRe;
    }

    public boolean chkIdSelf(Connection conn, String strUserId, String strTableNm) throws Exception{
        boolean blRe = false;

        String strQuery = "SELECT COUNT(USERID) FROM " + strTableNm + " WHERE USERID = '" + StringUtils.replace(strUserId, "'", "''") + "'";

        try{
            QueryRunner qRunner = new QueryRunner();

            ResultSetHandler rsh = new ScalarHandler(1);

            int cnt = ((Number)qRunner.query(conn, strQuery, rsh)).intValue();

            if(cnt > 0){
                blRe = true;
            }
        }
        finally{
            ;
        }

        return blRe;
    }

    public String getAddNum(Connection conn, String strUserId, int iCnt, int iRownum, String strTableNm) throws Exception{
        String sRE = "";

        int iLeng = strUserId.length() + 1;

        String sStart = "1";
        String sEnd   = "9";
        String sComp  = "@";

        for(int i=1;i<iCnt;i++){
            sStart += "0";
            sEnd   += "9";
            sComp  += "@";
        }

        StringBuffer sQuery = new StringBuffer()
                             .append("SELECT NUM                                                                                                        \n")
                             .append("  FROM(SELECT NUM                                                                                                 \n")
                             .append("            , ROWNUM RWNUM                                                                                        \n")
                             .append("         FROM(SELECT NUM                                                                                          \n")
                             .append("                FROM (SELECT     (" + sStart + " + LEVEL - 1) NUM                                                 \n")
                             .append("                        FROM      DUAL                                                                            \n")
                             .append("                      CONNECT BY (" + sStart + " + LEVEL - 1) <= " + sEnd + ")                                    \n")
                             .append("       MINUS                                                                                                      \n")
                             .append("       SELECT TO_NUMBER(SUBSTR(ID, " + iLeng + ")) NUM                                                            \n")
                             .append("         FROM G_MB_MEMBER                                                                                         \n")
                             .append("        WHERE ID LIKE '" + strUserId + "%'                                                                        \n")
                             .append("          AND TRANSLATE(SUBSTR(ID, " + iLeng + "), '0123456789@', '@@@@@@@@@@-') = '" + sComp + "')               \n")
                             .append("      )                                                                                                           \n")
                             .append(" WHERE RWNUM  = (SELECT (COUNT(*) + 1) CNT                                                                        \n")
                             .append("                   FROM " +  strTableNm + "                                                                       \n")
                             .append("                  WHERE USERID LIKE '" + strUserId + "%'                                                          \n")
                             .append("                    AND TRANSLATE(SUBSTR(USERID, " + iLeng + "), '0123456789@', '@@@@@@@@@@-') = '" + sComp + "') \n");

        try{
            QueryRunner qRunner = new QueryRunner();

            ResultSetHandler rsh = new ScalarHandler(1);

            sRE = ObjectUtils.toString(qRunner.query(conn, sQuery.toString(), rsh));
        }
        finally{
            ;
        }

        return sRE;
    }

    public String getNewUserId(Connection conn, String strUserId, String sTableNm) throws Exception{
        String strTempUserId = strUserId;
        String sGetAddNum = "";
        int intSeqForId = 0;
        int iGetNext = 0;

        try{
            while(true){
                if(!this.chkMemberId(conn, strTempUserId) && !this.chkIdSelf(conn, strTempUserId, sTableNm)){
                    //intSeqForId = 0;
                    //iGetNext = 0;
                    strUserId = strTempUserId;
                    break;
                }
                else{
                    while(true){
                        iGetNext++;
                        intSeqForId++;

                        sGetAddNum = this.getAddNum(conn, strUserId, intSeqForId, iGetNext, sTableNm);
                        if(!sGetAddNum.equals("")){
                            strTempUserId = strUserId + sGetAddNum;
                            break;
                        }
                    }
                }
            }
        }
        finally{
            ;
        }

        return strUserId;
    }

    public String getNewUserIdForRandom(Connection conn, String strUserId, String sTableNm, String strSpecialCH) throws Exception{
        String strTempUserId = strUserId;

        try{
            while(true){
                if(!this.chkMemberId(conn, strTempUserId) && !this.chkIdSelf(conn, strTempUserId, sTableNm)){
                    strUserId = strTempUserId;
                    break;
                }
                else{
                    strTempUserId = makeRandomData.makeDataUsIntSpChr(strSpecialCH, 4, "F");
                }
            }
        }
        finally{
            ;
        }

        return strUserId;
    }
}